﻿namespace Bizspoke.BizTalk.Adapter
{
    using System;
    using System.Data;
    using System.Data.SqlClient;

    public class SqlHelper
    {
        private static string _CatalogName;
        private static string _ConnectionString;
        private static string _DataSourceName;
        private static int _TransactionNumber;

        private SqlHelper()
        {
        }

        public static DataTable ExecuteProc(string proc, string[] args)
        {
            return ExecuteProc(proc, args, new EdiDbInfo());
        }

        public static DataTable ExecuteProc(string proc, string[] args, DbInfo dbInfo)
        {
            DataTable dataTable = null;
            SqlConnection connection = dbInfo.GetConnection();
            try
            {
                SqlCommand selectCommand = connection.CreateCommand();
                selectCommand.CommandType = CommandType.StoredProcedure;
                selectCommand.CommandText = proc;
                if (args != null)
                {
                    foreach (string str in args)
                    {
                        string[] strArray = str.Split(new char[] { '=' });
                        if (2 != strArray.Length)
                        {
                            throw new ArgumentException("Wrong number of separator ('=') in sub-argument: " + str, "args");
                        }
                        selectCommand.Parameters.Add(strArray[0], strArray[1]);
                    }
                }
                using (SqlDataAdapter adapter = new SqlDataAdapter(selectCommand))
                {
                    dataTable = new DataTable();
                    adapter.Fill(dataTable);
                    return dataTable;
                }
            }
            finally
            {
                if (ConnectionState.Open == connection.State)
                {
                    connection.Close();
                }
            }
            return dataTable;
        }

        public static DataTable ExecuteQuery(string query)
        {
            return ExecuteQuery(query, new EdiDbInfo());
        }

        public static DataTable ExecuteQuery(string query, DbInfo dbInfo)
        {
            DataTable dataTable = null;
            SqlConnection connection = dbInfo.GetConnection();
            try
            {
                SqlCommand selectCommand = connection.CreateCommand();
                selectCommand.CommandType = CommandType.StoredProcedure;
                selectCommand.CommandText = "sp_executesql";
                selectCommand.Parameters.Add("@statement", query);
                using (SqlDataAdapter adapter = new SqlDataAdapter(selectCommand))
                {
                    dataTable = new DataTable();
                    adapter.Fill(dataTable);
                    return dataTable;
                }
            }
            finally
            {
                if (ConnectionState.Open == connection.State)
                {
                    connection.Close();
                }
            }
            return dataTable;
        }

        public static int ExecuteTransaction(string statement)
        {
            return ExecuteTransaction(new string[] { statement }, new EdiDbInfo());
        }

        public static int ExecuteTransaction(string[] statements)
        {
            return ExecuteTransaction(statements, new EdiDbInfo());
        }

        public static int ExecuteTransaction(string[] statements, DbInfo dbInfo)
        {
            int num = 0;
            string transactionName = GenerateTransactionName();
            SqlConnection connection = dbInfo.GetConnection();
            connection.Open();
            SqlCommand command = connection.CreateCommand();
            SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted, transactionName);
            command.Connection = connection;
            command.Transaction = transaction;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "sp_executesql";
            try
            {
                foreach (string str2 in statements)
                {
                    command.Parameters.Clear();
                    command.Parameters.Add("@statement", str2);
                    num += command.ExecuteNonQuery();
                }
                transaction.Commit();
            }
            catch
            {
                transaction.Rollback(transactionName);
                throw;
            }
            finally
            {
                if (ConnectionState.Open == connection.State)
                {
                    connection.Close();
                }
            }
            return num;
        }

        public static string GenerateTransactionName()
        {
            lock (typeof(SqlHelper))
            {
                return ("EDI" + _TransactionNumber++);
            }
        }

        public static SqlConnection GetConnection()
        {
            return new SqlConnection(ConnectionString);
        }

        public static string CatalogName
        {
            get
            {
                if (_CatalogName == null)
                {
                    _CatalogName = Info.EdiDBName;
                }
                return _CatalogName;
            }
        }

        public static string ConnectionString
        {
            get
            {
                if (_ConnectionString == null)
                {
                    _ConnectionString = new EdiDbInfo().ConnectionString;
                }
                return _ConnectionString;
            }
        }

        public static string DataSourceName
        {
            get
            {
                if (_DataSourceName == null)
                {
                    _DataSourceName = Info.EdiDBServer;
                }
                return _DataSourceName;
            }
        }
    }
}

